package com.cqut.forum.dao.imp;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.springframework.stereotype.Component;

import com.cqut.forum.dao.PictureDao;
import com.cqut.forum.entity.Picture;
import com.cqut.forum.entity.User;

@Component
public class PictureDaoImp extends BaseDaoImp implements PictureDao{
	
	public <T> boolean unDelete(int id) {
		return false;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getCareUnread(int userId,int moduleId, int index,
			int size) {
		String sql = "select f.user_id,f.user_photo,f.user_name,p.picture_id,p.picture_address,p.upload_time,p.last_review_time " +
				"from (select uu.user_id,user_photo,user_name from user uu,relation r where " +
				"r.relation_level=\'好友\' and r.is_care=true and r.user_id = ? and r.friend_id = uu.user_id) as f,picture p " +
				"where p.owner_id = f.user_id and p.picture_state=true order by p.last_review_time desc";
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(userId);
		
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);
		
		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(sql, parameters, pages);
		return result;
	}
	
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getAllUnread(int userId,int moduleId, int index, int size) {
		String sql = "select f.user_id,f.user_photo,f.user_name,p.picture_id,p.picture_address,p.upload_time,p.last_review_time " +
				"from (select distinct uu.user_id,user_photo,user_name from user uu,relation r where " +
				"(r.relation_level=\'好友\' and r.user_id = ? and r.friend_id = uu.user_id) or (uu.user_id = ?)) as f,picture p " +
				"where p.owner_id = f.user_id and p.picture_state=true order by p.last_review_time desc";
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(userId);
		parameters.add(userId);
		
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);
		
		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(sql, parameters, pages);
		return result;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Picture> getMyUnread(int userId, int index, int size) {
		String hql = "select p from Picture p where p.ownerId=? and p.pictureState=true order by p.lastReviewTime desc";
		
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(userId);
		
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);
		
		List<Picture> result = (List<Picture>) super.excuteHql(hql, parameters, pages);
		
		return result;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getByGroupId(int groupId,int index,int size
			,String orderCase,boolean asc) {
		String sql = "select p.picture_id,p.picture_address,p.upload_time,p.review_times," +
				"p.visit_times from picture p where p.picture_group_id = ? and p.picture_state=true order by " +orderCase+" "+(asc?"asc":"desc")+
				" limit "+index+","+size;
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(groupId);
		//parameters.add(index);
		
		/*Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);*/
//		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(sql, parameters, pages);
		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(sql, parameters, null);
		return result;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getFriendsPicture(int userId,int index,int size) {
		/*String sql = "select p from picture p where p.owner_id in " +
				"(select r.friend_id from relation r where r.user_id = ?)";*/
		String sql = "select p.picture_id,p.upload_time,p.review_times,p.picture_address,u.user_photo,u.user_name " +
		"from picture p,user u where p.owner_id = u.user_id and (p.owner_id = ? or p.owner_id in " +
		"(select r.friend_id from relation r where r.user_id = ?)) and p.picture_state=true and p.is_public=true order by p.upload_time";
		
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(userId);
		parameters.add(userId);
		
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);
		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(sql, parameters, pages);
		return result;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getLastPicture(int index,int size) {
		String sql = "select p.upload_time,p.review_times,p.picture_address,u.user_photo,u.user_name " +
				"from picture p,user u where p.owner_id = u.user_id and p.picture_state=true and p.is_public=true order by p.upload_time desc";
		
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);
		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(sql, null, pages);
		return result;
	}
	
	public boolean saveList(List<Picture> pictures){
		for(Picture p:pictures){
			super.save(p);
		}
		return true;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getMyAllPicture(int userId, int index,
			int size,String orderCase,boolean asc) {
/*		m.put("pictureId", p.getPictureId());
		m.put("image", p.getPictureAddress());
		m.put("publishTime", p.getUploadTime().toLocaleString());
		m.put("replyTime", p.getReviewTimes());
		m.put("visitTime", p.getVisitTimes());*/
/*		String sql = "select p.picture_id,p.picture_address,p.upload_time,p.review_times," +
				"p.visit_times from picture p where p.owner_id = ? order by p.uploadTime desc";*/
		String sql = "select p.picture_id,p.picture_address,p.upload_time,p.review_times," +
				"p.visit_times from picture p where p.owner_id = ? and p.picture_state=true order by "+orderCase+" "+(asc?"asc":"desc");
		
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(userId);
		
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);
		
		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(sql, parameters, pages);
		return result;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getHotPicture(int index, int size) {
		String sql = "select p.picture_group_id,p.upload_time,p.picture_id,p.review_times,p.picture_address,u.user_photo,u.user_name " +
				"from picture p,picture_group pg,user u where p.owner_id = u.user_id and p.picture_state=true and p.is_public=true " +
				"and p.picture_group_id=pg.picture_group_id and pg.is_public=true order by p.review_times desc";
		
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);
		
		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(sql, null, pages);
		
		return result;
	}

	@SuppressWarnings("unchecked")
	@Override
	public int getPictureIndex(int groupId,int pictureId) {
		String sql = "select count(*) from picture p,picture_group pg where p.picture_id<?" +
				" and p.picture_group_id=? and p.picture_group_id=pg.picture_group_id";
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(pictureId);
		parameters.add(groupId);
		List<Map<String,BigInteger>> result = (List<Map<String,BigInteger>>)super.excuteSql(sql,parameters,null);
		return (Integer)(result.get(0).get("count(*)").intValue());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getByLikeName(String groupName, int index,
			int size) {
/*		map.put("publishTime", ((Date)m.get("upload_time")).toLocaleString());
		map.put("replyTime", m.get("review_times"));
		map.put("pictureSrc", m.get("picture_address"));
		map.put("userPhoto", m.get("user_photo"));
		map.put("userName", m.get("user_name"));
		String sql = "select p.picture_id,p.upload_time,p.review_times,p.picture_address,u.user_photo,u.user_name " +
		"from picture p,user u where p.owner_id = u.user_id and (p.owner_id = ? or p.owner_id in " +
		"(select r.friend_id from relation r where r.user_id = ?)) order by p.upload_time";
*/		String sql = "select p.picture_id,p.upload_time,p.review_times,p.picture_address,u.user_photo,u.user_name " +
					 "from picture p,user u where p.owner_id=u.user_id and p.picture_state=true and p.is_public=true and p.picture_group_id in" +
					 "(select pg.picture_group_id from picture_group pg where pg.is_public=true and pg.picture_group_name like \"%"+groupName+"%\")";
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);
		
		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(sql, null, pages);
		
		return result;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> loadPicture(String pictureOwner,
			String pictureType, String uploadTime, int index, int size) {
		String sql = "select u.user_name,p.picture_id,p.picture_address,p.upload_time," +
				"p.review_times,pg.picture_group_name from picture p,user u,picture_group pg where u.user_id=p.owner_id " +
				"and p.picture_group_id=pg.picture_group_id and p.picture_state=true and p.is_public=true";
		
		if(!pictureOwner.equals("")){
			sql = sql + " and u.user_name like \"%"+pictureOwner+"%\" ";
		}
		if(!pictureType.equals("")){
			sql = sql + " and pg.picture_group_name like \"%"+pictureType+"%\" ";
		}
		if(null!=uploadTime&&!"".equals(uploadTime)){
			sql = sql + " and p.upload_time>\""+uploadTime+"\"";
		}
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);
		
		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(sql, null, pages);
		
		return result;
	}

	@Override
	public boolean updatePictureState(int pictureId, boolean state) {
		String sql = "update picture p set p.picture_state=? where p.picture_id=?";
		
		Query query = super.getSqlQuery(sql);
		query.setBoolean(0, state);
		query.setInteger(1, pictureId);
		query.executeUpdate();
		return true;
	}

	@SuppressWarnings("unchecked")
	@Override
	public int count(boolean state) {
		String sql = "select count(p.picture) from picture p where " +
		"and p.picture_state=?";
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(state);
		
		List<Map<String, BigInteger>> result = (List<Map<String, BigInteger>>) super.excuteSql(sql, parameters, null);
		return (Integer)(result.get(0).get("count(a.article_id)").intValue());
	}

	@SuppressWarnings("unchecked")
	@Override
	public User getPictureOwner(int pictureId) {
		String hql = "select u from User u,Picture p where p.pictureId=? and p.ownerId=u.userId";
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(pictureId);
		
		List<User> result = (List<User>) super.excuteHql(hql, parameters, null);
		return result.size()>0?result.get(0):null;
	}

}
